<?php
/**
 * Created by PhpStorm.
 * User: xuliulei
 * Date: 2017/11/22 0022
 * Time: 11:11
 */
#error_reporting(0);
$dsn = 'mysql:host=localhost;port=3306;dbname=zgdzdb';
$usr = 'root';
$pwd = '';
$opt = [1002 => 'set names utf8'];
$pre = 'zg_';
try {
    $db = new pdo($dsn, $usr, $pwd, $opt);
} catch (PDOException $e) {
    exit('PDO MySQL Server Connection Fail...');
}

/**
 * <p>功能：实现数据插入功能，并返回插入后的id</p>
 * @param $tablename
 * @param $data
 * @return int|string
 */
function insert($tablename, $data)
{
    global $db, $pre;
    $db->beginTransaction();
    $rows = -1;
    $tn = $pre . $tablename;
    foreach ($data as $key => $V) {
        $keys[] = $key;
        $vs[] = ':' . $key;
    }
    $keys = implode(',', $keys);
    $vs = implode(',', $vs);
    $sql = "insert into $tn($keys) values($vs)";
    $stmt = $db->prepare($sql);
    $stmt->execute($data);
    $rows = $db->lastInsertId();
    $stmt->closeCursor();
    $db->commit();
    return $rows;
}

/**
 * <p>功能：实现数据插入功能，并返回插入后的id</p>
 * @param $sql
 * @param $data
 * @return string
 */
function save($sql, $data)
{
    global $db;
    $stmt = $db->prepare($sql);
    $stmt->execute($data);
    $stmt->closeCursor();
    return $db->lastInsertId();
}

function delete($tablename, $where = '1=1')
{
    global $db, $pre;
    $tn = $pre . $tablename;
    $num = 0;
    $db->beginTransaction();
    $stmt = $db->prepare("delete from $tn where $where ");
    $stmt->execute();
    $num = $stmt->rowCount();
    $stmt->closeCursor();
    $db->commit();
    return $num;
}

function del($sql, $param)
{
    global $db;
    $num = 0;
    $stmt = $db->prepare($sql);
    $stmt->execute($param);
    $num = $stmt->rowCount();
    $stmt->closeCursor();
    return $num;
}

function deleteById($tablename, $pkv)
{
    global $db, $pre;
    $num = 0;
    $tn = $pre . $tablename;
    $pk = getPk($tablename);
    $stmt = $db->prepare("delete from $tn where $pk=?");
    $stmt->execute([$pkv]);
    $stmt->closeCursor();
    $num = $stmt->rowCount();
    return $num;
}

function update($tablename, $data, $where = '1=1')
{
    global $db, $pre;
    $tn = $pre . $tablename;
    $pk = getPk($tablename);
    $num = 0;
    foreach ($data as $k => $v) {
        if ($k == $pk) continue;
        $fs[] = $k . '=:' . $k;
    }
    $fs = implode(',', $fs);
    if (array_key_exists($pk, $data)) {
        $stmt = $db->prepare("update $tn set $fs where $pk=:$pk");
    } else {
        $stmt = $db->prepare("update $tn set $fs where $where");
    }
    $stmt->execute($data);
    $num = $stmt->rowCount();
    $stmt->closeCursor();
    return $num;
}

function updates($sql, $param)
{
    global $db, $pre;
    $num = 0;
    $stmt = $db->prepare($sql);
    $stmt->execute($param);
    $num = $stmt->rowCount();
    $stmt->closeCursor();
    return $num;
}

function getPk($tablename)
{
    global $db, $pre;
    $pk = '';
    $tn = $pre . $tablename;
    $stmt = $db->prepare("SHOW FULL COLUMNS FROM $tn");
    $stmt->execute();
    $rows = $stmt->fetchAll(2);
    foreach ($rows as $v) {
        if ($v['Key'] == 'PRI') {
            $pk = $v['Field'];
            break;
        }
    }
    return $pk;
}

function getPass($password, $username = 'webrx')
{
    $p = $password . $username;
    $p1 = md5($p);
    $p2 = sha1($p);
    $ps = substr($p1, 0, 5);
    $ps .= substr($p2, 0, 5);
    $ps .= substr($p1, 10, 5);
    $ps .= substr($p2, 10, 5);
    $ps .= substr($p1, 15, 5);
    $ps .= substr($p2, 15, 5);
    $ps .= substr($p2, 30, 2);
    return strtolower($ps);
}

function getCount($tablename, $where = '1=1')
{
    global $db, $pre;
    $tn = $pre . $tablename;
    $stmt = $db->prepare("select count(*) from $tn where $where");
    $stmt->execute();
    $num = 0;
    $rows = $stmt->fetchAll(PDO::FETCH_NUM);
    $num = $rows[0][0];
    $stmt->closeCursor();
    return $num;
}

function query($tablename, $fields = '*', $where = '1=1', $order = '', $limit = '')
{
    global $db, $pre;
    $sql = sprintf('select %s from %s where %s %s %s', $fields, $pre . $tablename, $where, $order, $limit);
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    return $rows;
}

function queryById($tablename, $pk, $field = '*')
{
    global $db, $pre;
    $rows = NULL;
    $sql = sprintf('select %s from %s where %s=%s', $field, $pre . $tablename, getPk($tablename), $pk);
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $rows = array_shift($rows);
    $stmt->closeCursor();
    return $rows;
}

function page($tablename, $fields = '*', $pagesize = 5, $where = '1=1', $order = 'order by id desc', $type = '')
{
    global $db, $pre;
    $tn = $pre . $tablename;
    $info['recordcount'] = getCount($tablename, $where);
    $info['pagesize'] = $pagesize;
    $info['pagecount'] = ceil($info['recordcount'] / $info['pagesize']);
    $currpage = isset($_GET['p']) ? $_GET['p'] : 1;
    if ($currpage < 1) $currpage = 1;
    if ($currpage > $info['pagecount']) $currpage = $info['pagecount'];
    $info['currpage'] = $currpage;
    $stmt = $db->prepare("select $fields from $tn where $where $order limit ?,?");
    $stmt->bindValue(1, $currpage * $info['pagesize'] - $info['pagesize'], PDO::PARAM_INT);
    $stmt->bindValue(2, $pagesize, PDO::PARAM_INT);
    $stmt->execute();
    $info['rows'] = $stmt->fetchAll(2);
    $stmt->closeCursor();

    $page = '<div class="page">';
    $ss = 1;
    $ee = 9;
    if ($currpage > 5) {
        $ss = $currpage - 4;
        $ee = $currpage + 4;
        if ($currpage >= $info['pagecount'] - 4 && $info['pagecount'] > 4) {
            $ss = $info['pagecount'] - 8;
            $ee = $info['pagecount'];
        }
    }
    if ($currpage > 1) {
        $page .= sprintf('<a href="?p=%d" title="上一页">上一页</a>', $info['currpage'] - 1);
    } else {
        $page .= sprintf('<span class="prev">上一页</span>');
    }
    if ($currpage >= 6) {
        $page .= sprintf('<a href="?p=1" title="首页">1</a><span class="slh">…</span>');
    }
    for ($i = $ss; $i <= $ee; $i++) {
        if ($i > $info['pagecount']) break;
        if ($i == $currpage) {
            $page .= sprintf('<span>%d</span>', $i);
            continue;
        }
        $page .= sprintf('<a href="?p=%d">%d</a>', $i, $i);
    }
    if ($currpage < $info['pagecount'] - 4) {
        $page .= sprintf('<span class="slh">...</span><a href="?p=%d" title="末页">%d</a>', $info['pagecount'], $info['pagecount']);
    }
    if ($currpage < $info['pagecount']) {
        $page .= sprintf('<a href="?p=%d" title="下一页">下一页</a>', $info['currpage'] + 1);
    } else {
        $page .= sprintf('<span class="prev">下一页</span>');
    }
    $page .= sprintf('<span class="prev">每页 <strong>%d</strong> 条，共 <strong>%d</strong> 条</span>', $info['pagesize'], $info['recordcount']);

    $page .= '</div>';
    $info['page'] = $page;
    return $info;
}

function pageajax($tablename, $fields = '*', $pagesize = 5, $where = '1=1', $order = 'order by id desc', $type = '')
{
    global $db, $pre;
    $tn = $pre . $tablename;
    $info['recordcount'] = getCount($tablename, $where);
    $info['pagesize'] = $pagesize;
    $info['pagecount'] = ceil($info['recordcount'] / $info['pagesize']);
    $currpage = isset($_GET['p']) ? $_GET['p'] : 1;
    if ($currpage < 1) $currpage = 1;
    if ($currpage > $info['pagecount']) $currpage = $info['pagecount'];
    $info['currpage'] = $currpage;
    $stmt = $db->prepare("select $fields from $tn where $where $order limit ?,?");
    $stmt->bindValue(1, $currpage * $info['pagesize'] - $info['pagesize'], PDO::PARAM_INT);
    $stmt->bindValue(2, $pagesize, PDO::PARAM_INT);
    $stmt->execute();
    $info['rows'] = $stmt->fetchAll(2);
    $stmt->closeCursor();

    $page = '<div class="page">';
    $ss = 1;
    $ee = 9;
    if ($currpage > 5) {
        $ss = $currpage - 4;
        $ee = $currpage + 4;
        if ($currpage >= $info['pagecount'] - 4 && $info['pagecount'] > 4) {
            $ss = $info['pagecount'] - 8;
            $ee = $info['pagecount'];
        }
    }
    if ($currpage > 1) {
        $page .= sprintf('<a href="javascript:void(0)" title="上一页" p="%d">上一页</a>', $info['currpage'] - 1);
    } else {
        $page .= sprintf('<span class="prev">上一页</span>');
    }
    if ($currpage >= 6) {
        $page .= sprintf('<a href="javascript:void(0)" title="首页" p="1">1</a><span class="slh">…</span>');
    }
    for ($i = $ss; $i <= $ee; $i++) {
        if ($i > $info['pagecount']) break;
        if ($i == $currpage) {
            $page .= sprintf('<span>%d</span>', $i);
            continue;
        }
        $page .= sprintf('<a href="javascript:void(0)" p="%d">%d</a>', $i, $i);
    }
    if ($currpage < $info['pagecount'] - 4) {
        $page .= sprintf('<span class="slh">...</span><a href="javascript:void(0)" p="%d" title="末页">%d</a>', $info['pagecount'], $info['pagecount']);
    }
    if ($currpage < $info['pagecount']) {
        $page .= sprintf('<a href="javascript:void(0)" title="下一页" p="%d">下一页</a>', $info['currpage'] + 1);
    } else {
        $page .= sprintf('<span class="prev">下一页</span>');
    }
    $page .= sprintf('<span class="prev">每页 <strong>%d</strong> 条，共 <strong>%d</strong> 条</span>', $info['pagesize'], $info['recordcount']);

    $page .= '</div>';
    $info['page'] = $page;
    return $info;
}

function pagecss()
{
    $pcss = <<<EOD
        .page{display:block;margin:0 auto;max-width:750px}.page,.page a{text-align:center}.page a{display:inline-block;margin:0 3px;padding:6px;min-width:9pt;border:1px solid #f1f1f1;border-radius:3px;color:#333;text-decoration:none;font:9pt/1.2 '微软雅黑'}.page a:hover{border:1px solid #dfdfdf;background-color:#fbfbfb;font-weight:400}.page span{display:inline-block;margin:0 3px;padding:6px;border:1px solid transparent;border-radius:3px;background-color:#fbfbfb;color:#333;text-align:center;text-decoration:none;font:9pt/1.2 '微软雅黑';font-weight:700}.page span.prev{border:1px solid #dfdfdf;font-weight:400}.page span.slh{margin:0;min-width:10px}
EOD;
    return $pcss;
}